﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OneMile.Entity.Model;
using System.Data.SqlClient;
using OneMile.Entity;
using System.Data;
using OneMile.Common;
using DALBase;
using System.Collections;

namespace OneMile.DAL
{
   public  class DALUShops:BaseDAL
    {
       protected internal DBAccess db = DBConnection.CreateDB();

       

       private string GetSortedSQL(string sql, int SortField,int SortType)
       {
          String sql2=null;
          switch (SortField)
           {
               case (int)Enums.EnumShopSortField.ShopsName:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.ShopsName.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumShopSortField.ShopsPoor:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.ShopsPoor.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumShopSortField.ShopsPraise:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.ShopsPraise.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumShopSortField.ShopsTel:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.ShopsTel.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumShopSortField.ShopsType:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.ShopsType.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumShopSortField.ShopsAddress:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.ShopsAddress.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumShopSortField.RecommendedDishes:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.RecommendedDishes.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumShopSortField.OpeningHours:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.OpeningHours.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumShopSortField.LowestPrice:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.LowestPrice.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumShopSortField.Freight:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.Freight.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumShopSortField.Consumption:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.Consumption.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               default:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.ShopsName.ToString(), "DESC");
                   break;
            }
           return sql2;
       }

       /// <summary>
       /// 为了距离排序专门的方法
       /// </summary>
       /// <param name="shopType"></param>
       /// <returns></returns>
       public IList<EShopsInfo> GetShopListByType(string shopType)
       {
           try
           {
               StringBuilder sb = new StringBuilder();
               sb.AppendLine("SELECT (SELECT COUNT(1) FROM Fct_Coupon b WHERE b.Disabled=0 AND (GETDATE() BETWEEN b.StartDate AND DATEADD(DAY,1,b.EndDate)) AND b.ShopsId=a.ShopsId) as CouponNum,");
               sb.AppendLine("       ShopsId,ShopsName,ShopsType,ShopsPraise,ShopsPoor,Consumption,RecommendedDishes,DeliveryDistance,LowestPrice,Freight,Latitude,Longitude,OpeningTime,ClosingTime");
               sb.AppendLine("  FROM Fct_Shops a");
               sb.AppendLine(" WHERE Disabled=0 AND ShopsType LIKE @ShopType");
               Hashtable hs = new Hashtable();
               hs.Add("ShopType", String.Format("%{0}%",shopType));

               DataTable dt=db.ExecuteQuery(sb.ToString(),hs);
               IList<EShopsInfo> list = null;
               if (dt.Rows.Count > 0)
               {
                   list = new List<EShopsInfo>();
                   EShopsInfo si = null;
                   for (int i = 0; i < dt.Rows.Count; i++)
                   {
                       si = new EShopsInfo();
                       si.ShopsId = new Guid(dt.Rows[i]["ShopsId"].TString());
                       si.CouponNum = dt.Rows[i]["CouponNum"].TInt32();
                       si.ShopsName = dt.Rows[i]["ShopsName"].TString();
                       si.ShopsType = dt.Rows[i]["ShopsType"].TString();
                       si.ShopsPraise = dt.Rows[i]["ShopsPraise"].TInt32();
                       si.ShopsPoor = dt.Rows[i]["ShopsPoor"].TInt32();
                       si.Consumption = dt.Rows[i]["Consumption"].TDecimal();
                       si.RecommendedDishes = dt.Rows[i]["RecommendedDishes"].TString();
                       si.DeliveryDistance = dt.Rows[i]["DeliveryDistance"].TDecimal();
                       si.LowestPrice = dt.Rows[i]["LowestPrice"].TDecimal();
                       si.Freight = dt.Rows[i]["Freight"].TDecimal();
                       si.Latitude = dt.Rows[i]["Latitude"].TDouble();
                       si.Longitude = dt.Rows[i]["Longitude"].TDouble();
                       si.OpeningTime = dt.Rows[i]["OpeningTime"].TTime();
                       si.ClosingTime = dt.Rows[i]["ClosingTime"].TTime();
                       list.Add(si);
                   }
               }
               return list;
           }
           catch (Exception e)
           {
               base.WriteLog(e.Message);
               throw e;
           }
       }

       /// <summary>
       /// 
       /// </summary>
       /// <param name="shopName"></param>
       /// <param name="shopType"></param>
       /// <returns></returns>
       public IList<EShopsInfo> SearchShopListByName(string shopName, string shopType)
       {
           try
           {
               StringBuilder sb = new StringBuilder();
               sb.AppendLine("SELECT (SELECT COUNT(1) FROM Fct_Coupon b WHERE b.Disabled=0 AND (GETDATE() BETWEEN b.StartDate AND DATEADD(DAY,1,b.EndDate)) AND b.ShopsId=a.ShopsId) as CouponNum,");
               sb.AppendLine("       ShopsId,ShopsName,ShopsType,ShopsPraise,ShopsPoor,Consumption,RecommendedDishes,DeliveryDistance,LowestPrice,Freight,Latitude,Longitude,OpeningTime,ClosingTime");
               sb.AppendLine("  FROM Fct_Shops a");
               sb.AppendLine(" WHERE Disabled=0 AND ShopsType LIKE @ShopType AND ShopsName LIKE @ShopsName");

               Hashtable hs = new Hashtable();
               hs.Add("ShopsName", String.Format("%{0}%", shopName));
               hs.Add("ShopType", String.Format("%{0}%", shopType));

               DataTable dt = db.ExecuteQuery(sb.ToString(), hs);
               List<EShopsInfo> list = null;

               if (dt.Rows.Count > 0)
               {
                   list = new List<EShopsInfo>();
                   EShopsInfo shop = null;
                   for (int i = 0; i < dt.Rows.Count; i++)
                   {
                       shop = new EShopsInfo();
                       Guid ShopId = new Guid(dt.Rows[i]["ShopsId"].TString());
                       shop.ShopsId = ShopId;
                       shop.CouponNum = dt.Rows[i]["CouponNum"].TInt32();
                       shop.ShopsName = dt.Rows[i]["ShopsName"].TString();
                       shop.ShopsType = dt.Rows[i]["ShopsType"].TString();
                       shop.ShopsPraise = dt.Rows[i]["ShopsPraise"].TInt32();
                       shop.ShopsPoor = dt.Rows[i]["ShopsPoor"].TInt32();
                       shop.Consumption = dt.Rows[i]["Consumption"].TDecimal();
                       shop.RecommendedDishes = dt.Rows[i]["RecommendedDishes"].TString();
                       shop.DeliveryDistance = dt.Rows[i]["DeliveryDistance"].TDecimal();
                       shop.LowestPrice = dt.Rows[i]["LowestPrice"].TDecimal();
                       shop.Freight = dt.Rows[i]["Freight"].TDecimal();
                       shop.Latitude = dt.Rows[i]["Latitude"].TDouble();
                       shop.Longitude = dt.Rows[i]["Longitude"].TDouble();
                       shop.OpeningTime = dt.Rows[i]["OpeningTime"].TTime();
                       shop.ClosingTime = dt.Rows[i]["ClosingTime"].TTime();
                       list.Add(shop);
                   }
               }
               return list;
           }
           catch (Exception ex)
           {
               base.WriteLog("DALUShops_SearchShopListByName" + ex);
               return null;
           }
       }

       public IList<EShopsInfo> GetShopListByType(string shopType,int PageNo,int PageSize,int SortType,int SortField)
       {
           try
           {
               //           String sql = @"  SELECT ShopsId,ShopsName,ShopsType,ShopsPraise,ShopsPoor,Consumption,RecommendedDishes,DeliveryDistance,LowestPrice,Freight,Latitude,Longitude,OpeningTime,ClosingTime
               //                              FROM (
               //  		                            SELECT ShopsId,ShopsName,ShopsType,ShopsPraise,ShopsPoor,Consumption,RecommendedDishes,DeliveryDistance,LowestPrice,Freight,Latitude,Longitude,OpeningTime,ClosingTime
               //	 		                               ROW_NUMBER() OVER(ORDER BY {0} {1}) as RowNum
               //		                              FROM Fct_Shops 
               //		                             WHERE Disabled=0 
               //                                       AND LastLoginTime> convert(varchar(10),getdate(), 120)
               //                                       AND ShopsType LIKE @ShopType 
               //	                               ) AS Djmjmb 
               //                             WHERE RowNum BETWEEN (@PageNo-1)*@PageSize+1 AND @PageNo*@PageSize";
               String sql = @"SELECT (SELECT COUNT(1) FROM Fct_Coupon b WHERE b.Disabled=0 AND (GETDATE() BETWEEN b.StartDate AND DATEADD(DAY,1,b.EndDate)) AND b.ShopsId=D.ShopsId) as CouponNum,
                                       ShopsId,ShopsName,ShopsType,ShopsPraise,ShopsPoor,Consumption,RecommendedDishes,DeliveryDistance,LowestPrice,Freight,Latitude,Longitude,OpeningTime,ClosingTime
                              FROM (
  		                            SELECT ShopsId,ShopsName,ShopsType,ShopsPraise,ShopsPoor,Consumption,RecommendedDishes,DeliveryDistance,LowestPrice,Freight,Latitude,Longitude,OpeningTime,ClosingTime
	 		                               ROW_NUMBER() OVER(ORDER BY {0} {1}) as RowNum
		                              FROM Fct_Shops 
		                             WHERE Disabled=0 
                                       AND ShopsType LIKE @ShopType 
	                               ) AS D
                             WHERE RowNum BETWEEN (@PageNo-1)*@PageSize+1 AND @PageNo*@PageSize";

               sql = GetSortedSQL(sql, SortField, SortType);


               Hashtable hs = new Hashtable();
               hs.Add("ShopType", String.Format("%{0}%", shopType));
               hs.Add("PageNo", PageNo);
               hs.Add("PageSize", PageSize);

               DataTable dt = db.ExecuteQuery(sql, hs);
               List<EShopsInfo> list = null;
               if (dt.Rows.Count > 0)
               {
                   list = new List<EShopsInfo>();
                   EShopsInfo shop = null;
                   for (int i = 0; i < dt.Rows.Count; i++)
                   {
                       shop = new EShopsInfo();
                       Guid ShopId = new Guid(dt.Rows[i]["ShopSId"].TString());
                       shop.ShopsId = ShopId;
                       shop.CouponNum = dt.Rows[i]["CouponNum"].TInt32();
                       shop.ShopsName = dt.Rows[i]["ShopsName"].TString();
                       shop.ShopsType = dt.Rows[i]["ShopsType"].TString();
                       shop.ShopsPraise = dt.Rows[i]["ShopsPraise"].TInt32();
                       shop.ShopsPoor = dt.Rows[i]["ShopsPoor"].TInt32();
                       shop.Consumption = dt.Rows[i]["Consumption"].TDecimal();
                       shop.RecommendedDishes = dt.Rows[i]["RecommendedDishes"].TString();
                       shop.DeliveryDistance = dt.Rows[i]["DeliveryDistance"].TDecimal();
                       shop.LowestPrice = dt.Rows[i]["LowestPrice"].TDecimal();
                       shop.Freight = dt.Rows[i]["Freight"].TDecimal();
                       shop.Latitude = dt.Rows[i]["Latitude"].TDouble();
                       shop.Longitude = dt.Rows[i]["Longitude"].TDouble();
                       shop.OpeningTime = dt.Rows[i]["OpeningTime"].TTime();
                       shop.ClosingTime = dt.Rows[i]["ClosingTime"].TTime();
                       list.Add(shop);
                   }
               }
               return list;
           }
           catch (Exception ex)
           {
               base.WriteLog("DALUShops_GetShopListByType" + ex);
               throw ex;
           }
       }


       public IList<EShopsInfo> SearchShopListByName(string shopName, int PageNo, int PageSize, int SortType, int SortField)
       {
                                                  
//           String sql = @"  SELECT ShopsId,ShopsName,ShopsType,ShopsPraise,ShopsPoor,Consumption,RecommendedDishes,DeliveryDistance,LowestPrice,Freight,Latitude,Longitude,OpeningTime,ClosingTime
//                              FROM (
//  		                            SELECT ShopsId,ShopsName,ShopsType,ShopsPraise,ShopsPoor,Consumption,RecommendedDishes,DeliveryDistance,LowestPrice,Freight,Latitude,Longitude,OpeningTime,ClosingTime,
//	 		                               ROW_NUMBER() OVER(ORDER BY {0} {1}) as RowNum
//		                              FROM Fct_Shops 
//		                             WHERE Disabled=0
//                                     AND LastLoginTime> convert(varchar(10),getdate(), 120) 
//                                       AND ShopsName LIKE @ShopsName 
//	                               ) AS D
//                             WHERE RowNum BETWEEN (@PageNo-1)*@PageSize+1 AND @PageNo*@PageSize";
           String sql = @"  SELECT (SELECT COUNT(1) FROM Fct_Coupon b WHERE b.Disabled=0 AND (GETDATE() BETWEEN b.StartDate AND DATEADD(DAY,1,b.EndDate)) AND b.ShopsId=D.ShopsId) as CouponNum,
                                    ShopsId,ShopsName,ShopsType,ShopsPraise,ShopsPoor,Consumption,RecommendedDishes,DeliveryDistance,LowestPrice,Freight,Latitude,Longitude,OpeningTime,ClosingTime
                              FROM (
  		                            SELECT ShopsId,ShopsName,ShopsType,ShopsPraise,ShopsPoor,Consumption,RecommendedDishes,DeliveryDistance,LowestPrice,Freight,Latitude,Longitude,OpeningTime,ClosingTime,
	 		                               ROW_NUMBER() OVER(ORDER BY {0} {1}) as RowNum
		                              FROM Fct_Shops 
		                             WHERE Disabled=0 
                                       AND ShopsName LIKE @ShopsName 
	                               ) AS D
                             WHERE RowNum BETWEEN (@PageNo-1)*@PageSize+1 AND @PageNo*@PageSize";

           sql = GetSortedSQL(sql, SortField, SortType);
          
           try
           {
               Hashtable hs = new Hashtable();
               hs.Add("ShopsName",String.Format("%{0}%", shopName));
               hs.Add("PageNo",PageNo);
               hs.Add("PageSize",PageSize);

               DataTable dt=db.ExecuteQuery(sql,hs);
               List<EShopsInfo> list = null;
               
               if (dt.Rows.Count > 0)
               {
                   list = new List<EShopsInfo>();
                   EShopsInfo shop = null;
                   for (int i = 0; i < dt.Rows.Count; i++)
                   {
                       shop = new EShopsInfo();
                       Guid ShopId = new Guid(dt.Rows[i]["ShopSId"].TString());
                       shop.ShopsId = ShopId;
                       shop.CouponNum = dt.Rows[i]["CouponNum"].TInt32();
                       shop.ShopsName = dt.Rows[i]["ShopsName"].TString();
                       shop.ShopsType = dt.Rows[i]["ShopsType"].TString();
                       shop.ShopsPraise = dt.Rows[i]["ShopsPraise"].TInt32();
                       shop.ShopsPoor = dt.Rows[i]["ShopsPoor"].TInt32();
                       shop.Consumption = dt.Rows[i]["Consumption"].TDecimal();
                       shop.RecommendedDishes = dt.Rows[i]["RecommendedDishes"].TString();
                       shop.DeliveryDistance = dt.Rows[i]["DeliveryDistance"].TDecimal();
                       shop.LowestPrice = dt.Rows[i]["LowestPrice"].TDecimal();
                       shop.Freight = dt.Rows[i]["Freight"].TDecimal();
                       shop.Latitude = dt.Rows[i]["Latitude"].TDouble();
                       shop.Longitude = dt.Rows[i]["Longitude"].TDouble();
                       shop.OpeningTime = dt.Rows[i]["OpeningTime"].TTime();
                       shop.ClosingTime = dt.Rows[i]["ClosingTime"].TTime();
                       list.Add(shop);
                   }
               }
               return list;
           }
           catch (Exception ex)
           {
               base.WriteLog("DALUShops_GetShopListByType" + ex);
               return null;
           }
       }

       public EShopsInfo GetShopDetailsByID(string ShopsId)
       {

           StringBuilder sb = new StringBuilder();
           sb.AppendLine("   SELECT Top 1 a.[ShopsId],[ShopsName],[ShopsAddress],[ShopsTel],[ShopsPraise],[ShopsPoor],[ShopsType]");
           sb.AppendLine("                ,[RecommendedDishes],[OpeningHours],[Consumption],[LowestPrice],[Freight],[LastLoginTime]");
           sb.AppendLine("                ,[PromotionsId],[PromotionsTitle] ,[DeliveryDistance],[OpeningTime],[ClosingTime]");
           sb.AppendLine("     FROM [LOM].[dbo].[Fct_Shops] a");
           sb.AppendLine("LEFT JOIN Fct_Promotions b on a.ShopsId=b.ShopsId");
           sb.AppendLine("    WHERE a.ShopsId=@ShopsId");
           sb.AppendLine(" ORDER BY b.Rec_CreateTime DESC");
           try
           {
               Hashtable hs = new Hashtable();
               hs.Add("ShopsId", ShopsId);
               DataTable dt = db.ExecuteQuery(sb.ToString(), hs);
               EShopsInfo shop = null;
               if (dt.Rows.Count > 0)
               {
                   shop= new EShopsInfo();
                   Guid shopid = new Guid(dt.Rows[0]["ShopsId"].TString());
                   shop.ShopsId = shopid;
                   Guid promotionsid = new Guid(dt.Rows[0]["PromotionsId"].TString());
                   shop.PromotionsId = promotionsid;
                   shop.ShopsName = dt.Rows[0]["ShopsName"].TString();
                   shop.ShopsAddress = dt.Rows[0]["ShopsAddress"].TString();
                   shop.ShopsTel = dt.Rows[0]["ShopsTel"].TString();
                   shop.ShopsPraise = dt.Rows[0]["ShopsPraise"].TInt32();
                   shop.ShopsPoor = dt.Rows[0]["ShopsPoor"].TInt32();
                   shop.ShopsType = dt.Rows[0]["ShopsType"].TString();
                   shop.RecommendedDishes = dt.Rows[0]["RecommendedDishes"].TString();
                   shop.OpeningHours = dt.Rows[0]["OpeningHours"].TString();
                   shop.Consumption = dt.Rows[0]["Consumption"].TDecimal();
                   shop.LowestPrice = dt.Rows[0]["LowestPrice"].TDecimal();
                   shop.Freight = dt.Rows[0]["Freight"].TDecimal();
                   shop.LastLoginTime = dt.Rows[0]["LastLoginTime"].TString();
                   shop.PromotionsTitle = dt.Rows[0]["PromotionsTitle"].TString();
                   shop.DeliveryDistance = dt.Rows[0]["DeliveryDistance"].TDecimal();
                   shop.OpeningTime = dt.Rows[0]["OpeningTime"].TTime();
                   shop.ClosingTime = dt.Rows[0]["ClosingTime"].TTime();
               }
               return shop; ;
           }
           catch (Exception ex)
           {
               base.WriteLog("GetShopDetailsByID-" + ex);
               return null;
           }
       }


       public byte[] GetBigShopImgByShopId(string ShopId)
       {
           String sql = @"SELECT [ShopsImg]
                            FROM [Fct_Shops]
                           WHERE [ShopsId]=@ShopsId";
           try
           {
               List<SqlParameter> list = new List<SqlParameter>();
               list.Add(new SqlParameter("@ShopsId", ShopId));
               return (byte[])DBHelperSQL.sqlExecuteScalar(sql, list);
           }
           catch (Exception ex)
           {
               base.WriteLog("DALUShops_GetBigShopImgByShopId-" + ex);
               return null;
           }
       }
    }
}
